﻿using System;
using System.Data;
using System.Data.SqlClient;

using CNCSoft.EData;

using Common;

namespace CNCSoft.ErpApps.TMS
{
	/// <summary>
	/// Summary description for DanhMuc.
	/// </summary>
	public class DanhMuc {

		public enum TLDM {
			DoiTac,
			LyDoThu,
            LyDoThuHP,
            LyDoThuHP_A,
			LyDoChi,
            HocVien,
            SinhVien,

			HinhThucBanHang,
			HinhThucThanhToan,
	
            HinhThucEnq,
            GioiTinh,
            PhuongXa,
            Quan,
            ThanhPho,
            NgheNghiep,
            KenhThongTin,
            ChuongTrinhQuanTam,
            BangCap,
            KhoaHoc,
            Lop,
            GioHoc,
            NgayTrongTuan,
            BuoiHoc,
            KhaNangTaiChinh,

            Feeplan,
            FeePlanIncludeHistory,
            FeeplanDetail,

            ListAddNew,

            GiaoVien,
            NhanVienVanPhong,
            HinhThucKiemTra,
            MonHoc,
            MonThi,
            GioHocGiaoVu,

            TaiKhoan,
            ListUser,
            NganHang,

            Khoi,  //Khoi 1,2,3,... 12
            CoSo,   //Co so 1, co so 2
            CheDo,  //Ban tru, noi tru, ...
            QuanHe,  //Cha/Me/Anh/Chi
            HinhThucXetTuyen,
            Truong,
            HocLuc,
            HanhKiem,

            HocPhi_Dot,
            HoSoTuyenSinh,

            Location

		}

		public static void fill(DataTable tbl, TLDM dm) {
			switch (dm) {
                
                case TLDM.Khoi:
                    SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "Select * from DM_KHOI");
                    break;
                case TLDM.CoSo:
                    SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "Select * from DM_COSO");
                    break;
                case TLDM.CheDo:
                    SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "Select * from DM_CHEDO");
                    break;
                case TLDM.QuanHe:
                    SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "Select * from DM_QUANHE");
                    break;
                case TLDM.HinhThucXetTuyen:
                    SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "Select * from DM_HINHTHUCXETTUYEN");
                    break;
                case TLDM.Truong:
                    SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "Select DM_TRUONG.*, isnull(TenTinhTP,'') as TenTinhTP from DM_TRUONG left join DM_TINHTP ON DM_TRUONG.MaTinhTP = DM_TINHTP.MaTinhTP ");
                    break;
                case TLDM.HocLuc:
                    SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "Select * from DM_HOCLUC");
                    break;
                case TLDM.HanhKiem:
                    SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "Select * from DM_HANHKIEM");
                    break;
                case TLDM.HocPhi_Dot:
                    SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "Select * from DM_HOCPHI_DOT");
                    break;

				case TLDM.DoiTac:
                    SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "Select MaDoiTac, TenDoiTac, isnull(NguoiDaiDien,'') as NguoiDaiDien, isnull(MaSoThue,'') as MaSoThue, isnull(DiaChi,'') as DiaChi from DM_DoiTac");
					break;
                case TLDM.HocVien:
                    SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "SELECT HOCSINH_LOP.MaHocTap as MaDoiTac, Holot + ' ' + Ten  as TenDoiTac, " +
                                                                            "isnull(DiaChi +  ', ' + case TenTinhTP when N'Không biết' then '' else TenTinhTP end,'') as DiaChi, HOCSINH_LOP.MaLop, isnull(MaCheDo,'') MaCheDo, isnull(MaKhoi,'') MaKhoi " +
                                                                            "from HOCSINH_LOP  " +
                                                                            "join HOCSINH on HOCSINH_LOP.MaHocSinh = HOCSINH.MaHocSinh  " +
                                                                            "join DM_LOP on HOCSINH_LOP.MaLop = DM_LOP.MaLop  " +
                                                                            "left JOIN DM_TINHTP ON HOCSINH.MaTinhTP = DM_TinhTP.MaTinhTP  " +
                                                                            "order by STT, Ten, HoLot");
                    break;
                case TLDM.SinhVien:
                    SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "SELECT InvoiceNumber as MaDoiTac, Holot + ' ' + Ten  as TenDoiTac, " +
                                                                            "isnull(DiaChiThuongTru + ', ' + TenQuanHuyen + ', ' + TenTinhTP,'') as DiaChi, " +
                                                                            "MaLop " +
                                                                            "from Invoice " +
                                                                            "JOIN Enquiry ON Invoice.EnquiryNumber = Enquiry.EnquiryNumber " +
                                                                            "JOIN DM_QUANHUYEN ON Enquiry.QuanThuongTru = DM_QuanHuyen.MaQuanHuyen " +
                                                                            "JOIN DM_TINHTP ON Enquiry.ThanhPhoThuongTru = DM_TinhTP.MaTinhTP");
                    break;

				case TLDM.LyDoThu:
					SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "Select * from DM_NGHIEPVU_TAIKHOAN Where MaNghiepVu = '" +GlobalVariable.WSInfo["TienMat.PhieuThu.MaLoaiCT"] +"'");
					break;
                case TLDM.LyDoThuHP:
                    SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "Select * from DM_HOCPHI");
                    break;
                case TLDM.LyDoThuHP_A:
                    SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "Select * from DM_NGHIEPVU_TAIKHOAN where MaNghiepVu ='HP' AND (MaDonVi ='" + GlobalVariable.WSInfo.CompanyID + "' OR  MaDonVi = 'All')"  );
                    break;

				case TLDM.LyDoChi:
					SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "Select * from DM_NGHIEPVU_TAIKHOAN Where MaNghiepVu = '" +GlobalVariable.WSInfo["TienMat.PhieuChi.MaLoaiCT"] +"'");
					break;
				case TLDM.HinhThucBanHang:
					SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "Select * from DM_HINHTHUCBANHANG");
					break;
				case TLDM.HinhThucThanhToan:
					SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "Select * from DM_HINHTHUCTHANHTOAN");
					break;

                case TLDM.HinhThucEnq:
                    SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "Select * from DM_HINHTHUCENQ");
                    break;

                case TLDM.GioiTinh:
                    SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "Select * from DM_GIOITINH");
                    break;

                case TLDM.PhuongXa:
                    SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "Select * from DM_PHUONGXA");
                    break;
                case TLDM.Quan:
                    SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "Select * from DM_QUANHUYEN");
                    break;
                case TLDM.ThanhPho:
                    SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "Select * from DM_TINHTP");
                    break;
                
                case TLDM.NgheNghiep:
                    SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "Select * from DM_NGHENGHIEP");
                    break;
                case TLDM.KenhThongTin:
                    SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "Select * from DM_KENHTHONGTIN");
                    break;
                case TLDM.ChuongTrinhQuanTam:
                    SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "Select * from DM_CHUONGTRINHQUANTAM WHERE MaDonVi = '" +  GlobalVariable.WSInfo.CompanyID + "'");
                    break;
                case TLDM.BangCap:
                    SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "Select * from DM_BANGCAP  WHERE MaDonVi = '" +  GlobalVariable.WSInfo.CompanyID + "'");
                    break;
                case TLDM.KhoaHoc:
                    SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "Select * from DM_KHOAHOC WHERE MaDonVi = '" + GlobalVariable.WSInfo.CompanyID + "'");
                    break;
                case TLDM.Lop:
                    SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "Select * from DM_LOP");
                    break;
                case TLDM.BuoiHoc:
                    SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "Select * from DM_BUOIHOC");
                    break;
                case TLDM.GioHoc:
                    SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "Select * from DM_GIOHOC");
                    break;
                case TLDM.NgayTrongTuan:
                    SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "Select * from DM_NGAYTRONGTUAN");
                    break;
                case TLDM.KhaNangTaiChinh:
                    SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "Select * from DM_KHANANGTAICHINH");
                    break;

                case TLDM.Feeplan: 
                    SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "Select * from FEEPLAN WHERE HetApDung = 0 AND MaDonVi = '" +  GlobalVariable.WSInfo.CompanyID + "' ORDER BY NamBanHanh DESC, STT ");
                    break;
                case TLDM.FeePlanIncludeHistory:
                    SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "Select * from FEEPLAN ORDER BY NamBanHanh DESC");  //Khong co WHERE
                    break;
                case TLDM.FeeplanDetail:
                    SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "Select * from FEEPLANDETAIL");
                    break;

                case TLDM.ListAddNew:
                    SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "Select * from DM_MALOAICHUNGTU order by STT");
                    break;
                case TLDM.GiaoVien:
                    SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "Select MaGiaoVien, TenGiaoVien from DM_GIAOVIEN");
                    break;
                case TLDM.NhanVienVanPhong:
                    SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "Select MaGiaoVien, TenGiaoVien from DM_GIAOVIEN WHERE BoPhan like N'%Văn phòng%'");
                    break;
                case TLDM.HinhThucKiemTra:
                    SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "Select * from DM_HINHTHUCKIEMTRA ");
                    break;
                case TLDM.MonHoc:
                    SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "Select MaMonHoc, TenMonHoc from DM_MONHOC ");
                    break;
                case TLDM.MonThi:
                    SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "Select MaMonThi, TenMonThi from DM_MONTHI ");
                    break;

                case TLDM.TaiKhoan:
                    SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "Select MaTaiKhoan, TenTaiKhoanVN from DM_TAIKHOAN order by MaTaiKhoan");
                    break;
                case TLDM.NganHang:
                    SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "Select MaNganHang, TenNganHang, SoTaiKhoan from DM_NGANHANG ");
                    break;
                case TLDM.GioHocGiaoVu:
                    SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "Select MaTimeSlot, TenTimeSlot from DM_TIMESLOT_2H ");
                    break;

                case TLDM.ListUser:
                    SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "Select UserID, FullName from [SYS_List_User] WHERE IsLocked = 0 or IsLocked is null ");
                    break;
                
                case TLDM.HoSoTuyenSinh:
                    SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "Select * from DM_HOSOTUYENSINH");
                    break;

                case TLDM.Location:
                    SQLHelper.Fill(tbl, GlobalVariable.WSInfo.ConnectionString, "Select * from DM_LOCATION");
                    break;
			}
		}
	}
}
